{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks: Resit Questions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**List the SalesOrderNumber for the customer 'Good Toys' 'Bike World'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>salessordernumber</th>\n",
       "        <th>companyname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Bike World</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO71774</td>\n",
       "        <td>Good Toys</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, 'Bike World'), ('SO71774', 'Good Toys')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SalessOrderNumber, CompanyName FROM\n",
    "    SalesOrderHeader RIGHT JOIN CustomerAW ON (\n",
    "        SalesOrderHeader.CustomerID=CustomerAW.CustomerID)\n",
    "    WHERE CustomerAW.CompanyName IN ('Good Toys', 'Bike World')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**List the ProductName and the quantity of what was ordered by 'Futuristic Bikes'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>orderqty</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ML Mountain Seat/Saddle</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long-Sleeve Logo Jersey, L</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Classic Vest, S</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ML Mountain Seat/Saddle', 2),\n",
       " ('Long-Sleeve Logo Jersey, L', 2),\n",
       " ('Classic Vest, S', 3)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Product.Name, OrderQty FROM\n",
    "    Product RIGHT JOIN SalesOrderDetail ON (\n",
    "        Product.ProductID=SalesOrderDetail.ProductID) RIGHT JOIN\n",
    "    SalesOrderHeader ON (\n",
    "        SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID) RIGHT JOIN\n",
    "    CustomerAW ON (CustomerAW.CustomerID=SalesOrderHeader.CustomerID)\n",
    "    WHERE CustomerAW.CompanyName = 'Futuristic Bikes'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the name and addresses of companies containing the word 'Bike' (upper or lower case) and companies containing 'cycle' (upper or lower case). Ensure that the 'bike's are listed before the 'cycles's.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>companyname</th>\n",
       "        <th>addressline1</th>\n",
       "        <th>addressline2</th>\n",
       "        <th>city</th>\n",
       "        <th>stateprovince</th>\n",
       "        <th>countryregion</th>\n",
       "        <th>postalcode</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Bike Store</td>\n",
       "        <td>2251 Elliot Avenue</td>\n",
       "        <td>None</td>\n",
       "        <td>Seattle</td>\n",
       "        <td>Washington</td>\n",
       "        <td>United States</td>\n",
       "        <td>98104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Typical Bike Shop</td>\n",
       "        <td>One Dancing, Rr</td>\n",
       "        <td>No. 25 Box 8033</td>\n",
       "        <td>Round Rock</td>\n",
       "        <td>Texas</td>\n",
       "        <td>United States</td>\n",
       "        <td>78664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Advanced Bike Components</td>\n",
       "        <td>12345 Sterling Avenue</td>\n",
       "        <td>None</td>\n",
       "        <td>Irving</td>\n",
       "        <td>Texas</td>\n",
       "        <td>United States</td>\n",
       "        <td>75061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Area Bike Accessories</td>\n",
       "        <td>6900 Sisk Road</td>\n",
       "        <td>None</td>\n",
       "        <td>Modesto</td>\n",
       "        <td>California</td>\n",
       "        <td>United States</td>\n",
       "        <td>95354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Associated Bikes</td>\n",
       "        <td>5420 West 22500 South</td>\n",
       "        <td>None</td>\n",
       "        <td>Salt Lake City</td>\n",
       "        <td>Utah</td>\n",
       "        <td>United States</td>\n",
       "        <td>84101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Authorized Bike Sales and Rental</td>\n",
       "        <td>490 Ne 4th St</td>\n",
       "        <td>None</td>\n",
       "        <td>Renton</td>\n",
       "        <td>Washington</td>\n",
       "        <td>United States</td>\n",
       "        <td>98055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Basic Bike Company</td>\n",
       "        <td>15 East Main</td>\n",
       "        <td>None</td>\n",
       "        <td>Port Orchard</td>\n",
       "        <td>Washington</td>\n",
       "        <td>United States</td>\n",
       "        <td>98366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Best o&#x27; Bikes</td>\n",
       "        <td>250880 Baur Blvd</td>\n",
       "        <td>None</td>\n",
       "        <td>Saint Louis</td>\n",
       "        <td>Missouri</td>\n",
       "        <td>United States</td>\n",
       "        <td>63103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Big-Time Bike Store</td>\n",
       "        <td>9909 W. Ventura Boulevard</td>\n",
       "        <td>None</td>\n",
       "        <td>Camarillo</td>\n",
       "        <td>California</td>\n",
       "        <td>United States</td>\n",
       "        <td>93010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bike Dealers Association</td>\n",
       "        <td>9952 E. Lohman Ave.</td>\n",
       "        <td>None</td>\n",
       "        <td>Las Cruces</td>\n",
       "        <td>New Mexico</td>\n",
       "        <td>United States</td>\n",
       "        <td>88001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bike Experts</td>\n",
       "        <td>Lancaster Mall</td>\n",
       "        <td>None</td>\n",
       "        <td>Salem</td>\n",
       "        <td>Oregon</td>\n",
       "        <td>United States</td>\n",
       "        <td>97301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bike Universe</td>\n",
       "        <td>Nut Tree Factory</td>\n",
       "        <td>None</td>\n",
       "        <td>Vacaville</td>\n",
       "        <td>California</td>\n",
       "        <td>United States</td>\n",
       "        <td>95688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bike World</td>\n",
       "        <td>60025 Bollinger Canyon Road</td>\n",
       "        <td>None</td>\n",
       "        <td>San Ramon</td>\n",
       "        <td>California</td>\n",
       "        <td>United States</td>\n",
       "        <td>94583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bikes and Motorbikes</td>\n",
       "        <td>22580 Free Street</td>\n",
       "        <td>None</td>\n",
       "        <td>Toronto</td>\n",
       "        <td>Ontario</td>\n",
       "        <td>Canada</td>\n",
       "        <td>M4B 1V7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bikes for Kids and Adults</td>\n",
       "        <td>9900 Ronson Drive</td>\n",
       "        <td>None</td>\n",
       "        <td>Etobicoke</td>\n",
       "        <td>Ontario</td>\n",
       "        <td>Canada</td>\n",
       "        <td>M9W 3P3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bikes for Two</td>\n",
       "        <td>63 West Beaver Creek</td>\n",
       "        <td>None</td>\n",
       "        <td>Richmond Hill</td>\n",
       "        <td>Ontario</td>\n",
       "        <td>Canada</td>\n",
       "        <td>L4E 3M5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bold Bike Accessories</td>\n",
       "        <td>Rocky Mountain Pines Outlet</td>\n",
       "        <td>None</td>\n",
       "        <td>Loveland</td>\n",
       "        <td>Colorado</td>\n",
       "        <td>United States</td>\n",
       "        <td>80537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cash &amp; Carry Bikes</td>\n",
       "        <td>Lakeline Mall</td>\n",
       "        <td>None</td>\n",
       "        <td>Cedar Park</td>\n",
       "        <td>Texas</td>\n",
       "        <td>United States</td>\n",
       "        <td>78613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Coalition Bike Company</td>\n",
       "        <td>Corporate Office</td>\n",
       "        <td>None</td>\n",
       "        <td>El Segundo</td>\n",
       "        <td>California</td>\n",
       "        <td>United States</td>\n",
       "        <td>90245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Commendable Bikes</td>\n",
       "        <td>4781 Highway 95</td>\n",
       "        <td>None</td>\n",
       "        <td>Sandpoint</td>\n",
       "        <td>Idaho</td>\n",
       "        <td>United States</td>\n",
       "        <td>83864</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">192 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('A Bike Store', '2251 Elliot Avenue', None, 'Seattle', 'Washington', 'United States', '98104'),\n",
       " ('A Typical Bike Shop', 'One Dancing, Rr', 'No. 25 Box 8033', 'Round Rock', 'Texas', 'United States', '78664'),\n",
       " ('Advanced Bike Components', '12345 Sterling Avenue', None, 'Irving', 'Texas', 'United States', '75061'),\n",
       " ('Area Bike Accessories', '6900 Sisk Road', None, 'Modesto', 'California', 'United States', '95354'),\n",
       " ('Associated Bikes', '5420 West 22500 South', None, 'Salt Lake City', 'Utah', 'United States', '84101'),\n",
       " ('Authorized Bike Sales and Rental', '490 Ne 4th St', None, 'Renton', 'Washington', 'United States', '98055'),\n",
       " ('Basic Bike Company', '15 East Main', None, 'Port Orchard', 'Washington', 'United States', '98366'),\n",
       " (\"Best o' Bikes\", '250880 Baur Blvd', None, 'Saint Louis', 'Missouri', 'United States', '63103'),\n",
       " ('Big-Time Bike Store', '9909 W. Ventura Boulevard', None, 'Camarillo', 'California', 'United States', '93010'),\n",
       " ('Bike Dealers Association', '9952 E. Lohman Ave.', None, 'Las Cruces', 'New Mexico', 'United States', '88001'),\n",
       " ('Bike Experts', 'Lancaster Mall', None, 'Salem', 'Oregon', 'United States', '97301'),\n",
       " ('Bike Universe', 'Nut Tree Factory', None, 'Vacaville', 'California', 'United States', '95688'),\n",
       " ('Bike World', '60025 Bollinger Canyon Road', None, 'San Ramon', 'California', 'United States', '94583'),\n",
       " ('Bikes and Motorbikes', '22580 Free Street', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V7'),\n",
       " ('Bikes for Kids and Adults', '9900 Ronson Drive', None, 'Etobicoke', 'Ontario', 'Canada', 'M9W 3P3'),\n",
       " ('Bikes for Two', '63 West Beaver Creek', None, 'Richmond Hill', 'Ontario', 'Canada', 'L4E 3M5'),\n",
       " ('Bold Bike Accessories', 'Rocky Mountain Pines Outlet', None, 'Loveland', 'Colorado', 'United States', '80537'),\n",
       " ('Cash & Carry Bikes', 'Lakeline Mall', None, 'Cedar Park', 'Texas', 'United States', '78613'),\n",
       " ('Coalition Bike Company', 'Corporate Office', None, 'El Segundo', 'California', 'United States', '90245'),\n",
       " ('Commendable Bikes', '4781 Highway 95', None, 'Sandpoint', 'Idaho', 'United States', '83864'),\n",
       " ('Economy Bikes Company', '72502 Eastern Ave.', None, 'Bell Gardens', 'California', 'United States', '90201'),\n",
       " ('Eighth Bike Store', '2500 N Serene Blvd', '19th Floor', 'El Segundo', 'California', 'United States', '90245'),\n",
       " ('Elite Bikes', 'Po Box 8259024', None, 'Dallas', 'Texas', 'United States', '75201'),\n",
       " ('Elite Bikes', '9178 Jumping St.', None, 'Dallas', 'Texas', 'United States', '75201'),\n",
       " ('Engineered Bike Systems', '123 Camelia Avenue', None, 'Oxnard', 'California', 'United States', '93030'),\n",
       " ('Essential Bike Works', 'New Millhouse, 2583 Milton Park', None, 'Abingdon', 'England', 'United Kingdom', 'OX14 4SE'),\n",
       " ('Express Bike Services', '586 Fulham Road,', None, 'London', 'England', 'United Kingdom', 'SW6 SBY'),\n",
       " ('Extended Bike Sales', 'The Citadel Commerce Plaza', None, 'City Of Commerce', 'California', 'United States', '90040'),\n",
       " ('Extraordinary Bike Works', 'Town East Center', None, 'Mesquite', 'Texas', 'United States', '75149'),\n",
       " (\"Family's Favorite Bike Shop\", '25981 College Street', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H5'),\n",
       " (\"Family's Favorite Bike Shop\", '26910 Indela Road', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H5'),\n",
       " ('Famous Bike Shop', '999 West Georgia St.', None, 'Vancouver', 'Ontario', 'Canada', 'V5T 1Y9'),\n",
       " ('Farthermost Bike Shop', '99000 S. Avalon Blvd. Suite 750', None, 'Carson', 'California', 'United States', '90746'),\n",
       " ('Farthest Bike Store', '45259 Canada Way', None, 'Burnaby', 'British Columbia', 'Canada', 'V5G 4S4'),\n",
       " ('Fashionable Bikes and Accessories', 'Sports Store At Park City', None, 'Park City', 'Utah', 'United States', '84098'),\n",
       " ('Fifth Bike Store', '2502 Evergreen Ste E', None, 'Everett', 'Washington', 'United States', '98201'),\n",
       " ('First Bike Store', 'Kansas City Factory Outlet', None, 'Odessa', 'Missouri', 'United States', '64076'),\n",
       " ('Flawless Bike Shop', 'North County Square', None, 'Vista', 'California', 'United States', '92084'),\n",
       " ('Fleet Bikes', '54254 Pacific Ave.', None, 'Stockton', 'California', 'United States', '95202'),\n",
       " ('Friendly Bike Shop', 'Port Huron', None, 'Port Huron', 'Michigan', 'United States', '48060'),\n",
       " ('Friendly Bike Shop', '750 Lakeway Dr', None, 'Bellingham', 'Washington', 'United States', '98225'),\n",
       " ('Friendly Neighborhood Bikes', 'Johnson Creek', None, 'Johnson Creek', 'Wisconsin', 'United States', '53038'),\n",
       " ('Frugal Bike Shop', '2575 West 2700 South', None, 'Salt Lake City', 'Utah', 'United States', '84101'),\n",
       " ('Full-Service Bike Store', '9920 North Telegraph Rd.', None, 'Pontiac', 'Michigan', 'United States', '48342'),\n",
       " ('Fun Toys and Bikes', '6500 East Grant Road', None, 'Tucson', 'Arizona', 'United States', '85701'),\n",
       " ('Future Bikes', '67255 - 8th Street N.E., Suite 350', None, 'Calgary', 'Alberta', 'Canada', 'T2P 2G8'),\n",
       " ('Futuristic Bikes', '25136 Jefferson Blvd.', None, 'Culver City', 'California', 'United States', '90232'),\n",
       " ('Gear-Shift Bikes Limited', '2512-4th Ave Sw', None, 'Calgary', 'Alberta', 'Canada', 'T2P 2G8'),\n",
       " ('General Bike Corporation', '69251 Creditview Road', None, 'Mississauga', 'Ontario', 'Canada', 'L5B 3V4'),\n",
       " ('Genial Bike Associates', '99450 Highway 59 North', None, 'Humble', 'Texas', 'United States', '77338'),\n",
       " ('Genuine Bike Shop', '7760 N. Pan Am Expwy', None, 'San Antonio', 'Texas', 'United States', '78204'),\n",
       " ('Go-cart and Bike Specialists', '2509 W. Frankford', None, 'Carrollton', 'Texas', 'United States', '75006'),\n",
       " ('Good Bike Shop', '99433 S. Greenbay Rd.', None, 'Racine', 'Wisconsin', 'United States', '53182'),\n",
       " ('Great Bikes', 'Eastridge Mall', None, 'Casper', 'Wyoming', 'United States', '82601'),\n",
       " ('Greater Bike Store', '55 Lakeshore Blvd East', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V6'),\n",
       " ('Handy Bike Services', '399700 John R. Rd.', None, 'Madison Heights', 'Michigan', 'United States', '48071'),\n",
       " ('Highway Bike Shop', 'Simi @ The Plaza', None, 'Simi Valley', 'California', 'United States', '93065'),\n",
       " ('Home Town Bike Store', '955 Green Valley Crescent', None, 'Ottawa', 'Ontario', 'Canada', 'K4B 1S1'),\n",
       " ('Initial Bike Company', '789 West Alameda', None, 'Westminster', 'Colorado', 'United States', '80030'),\n",
       " ('Large Bike Shop', '600 Boul. Rene-levesque Ouest', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H7'),\n",
       " ('Largest Bike Store', '63 W Monroe', None, 'Chicago', 'Illinois', 'United States', '60610'),\n",
       " ('Lots of Bikes Storehouse', '4255 East Lies Road', None, 'Carol Stream', 'Illinois', 'United States', '60188'),\n",
       " ('Many Bikes Store', 'Receiving', None, 'Fullerton', 'California', 'United States', '92831'),\n",
       " ('Metro Bike Works', '250000 Eight Mile Road', None, 'Detroit', 'Michigan', 'United States', '48226'),\n",
       " ('Moderately-Priced Bikes Store', '67 Rainer Ave S', None, 'Renton', 'Washington', 'United States', '98055'),\n",
       " ('More Bikes!', '25600 E St Andrews Pl', None, 'Santa Ana', 'California', 'United States', '92701'),\n",
       " ('Mountain Bike Center', '6756 Mowry', None, 'Newark', 'California', 'United States', '94560'),\n",
       " ('Nice Bikes', 'Suite 800 2530 Slater Street', None, 'Ottawa', 'Ontario', 'Canada', 'K4B 1T7'),\n",
       " ('Novelty Bikes', '998 Forest Road', None, 'Saginaw', 'Michigan', 'United States', '48601'),\n",
       " ('Number One Bike Co.', 'Elmhurst Road, Goring', None, 'Reading', 'England', 'United Kingdom', 'RG7 5H7'),\n",
       " ('One Bike Company', '5 place Ville-Marie', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H7'),\n",
       " ('Online Bike Sellers', '678 Eastman Ave.', None, 'Midland', 'Michigan', 'United States', '48640'),\n",
       " ('Online Bike Warehouse', '32605 West 252 Mile Road, Suite 250', None, 'Aurora', 'Ontario', 'Canada', 'L4G 7N6'),\n",
       " ('Practical Bike Supply Company', 'Horizon Outlet', None, 'Woodbury', 'Minnesota', 'United States', '55125'),\n",
       " ('Preferred Bikes', 'Incom Sports Center', None, 'Ontario', 'California', 'United States', '91764'),\n",
       " ('Price-Cutter Discount Bikes', '6700 Boul Taschereau', None, 'Brossard', 'Quebec', 'Canada', 'J4Z 1C5'),\n",
       " ('Primary Bike Distributors', '965 De La Gauchetiere West', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H8'),\n",
       " ('Principal Bike Company', 'Mountain Square', None, 'Upland', 'California', 'United States', '91786'),\n",
       " ('Racing Bike Outlet', '225 South 314th Street', None, 'Federal Way', 'Washington', 'United States', '98003'),\n",
       " ('Rapid Bikes', '992 St Clair Ave East', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V7'),\n",
       " ('Remarkable Bike Store', 'Eastern Beltway Center', None, 'Las Vegas', 'Nevada', 'United States', '89106'),\n",
       " ('Rental Bikes', '99828 Routh Street, Suite 825', None, 'Dallas', 'Texas', 'United States', '75201'),\n",
       " ('Responsible Bike Dealers', 'Ward Parkway Center', None, 'Kansas City', 'Missouri', 'United States', '64106'),\n",
       " ('Roadway Bike Emporium', 'Medford', None, 'Medford', 'Oregon', 'United States', '97504'),\n",
       " ('Rugged Bikes', '48995 Evergreen Wy.', None, 'Everett', 'Washington', 'United States', '98201'),\n",
       " ('Sample Bike Store', '2530 South Colorado Blvd.', None, 'Denver', 'Colorado', 'United States', '80203'),\n",
       " ('Sample Bike Store', '2000 300th Street', None, 'Denver', 'Colorado', 'United States', '80203'),\n",
       " ('Scooters and Bikes Store', '258101 Nw Evergreen Parkway', None, 'Beaverton', 'Oregon', 'United States', '97005'),\n",
       " ('Sharp Bikes', '52560 Free Street', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V7'),\n",
       " ('Sixth Bike Store', 'Suite 25800 3401 - 10810th Avenue', None, 'Surrey', 'British Columbia', 'Canada', 'V3T 4W3'),\n",
       " ('Sleek Bikes', '44025 W. Empire', None, 'Denby', 'South Dakota', 'United States', '57716'),\n",
       " ('Small Bike Shop', 'Corp Ofc Accts Payable', None, 'El Segundo', 'California', 'United States', '90245'),\n",
       " ('Solid Bike Parts', 'Lincoln Square', None, 'Arlington', 'Texas', 'United States', '76010'),\n",
       " ('South Bike Company', 'Po Box 83270', None, 'Vancouver', 'British Columbia', 'Canada', 'V7L 4J4'),\n",
       " ('Standard Bikes', '9979 Bayview Drive', None, 'Barrie', 'Ontario', 'Canada', 'L4N'),\n",
       " ('Stationary Bikes and Stands', 'Sapp Road West', None, 'Round Rock', 'Texas', 'United States', '78664'),\n",
       " ('Tenth Bike Store', 'Depot 80', None, 'Sillery', 'Quebec', 'Canada', 'G1T'),\n",
       " ('The Bike Shop', '3250 South Meridian', None, 'Puyallup', 'Washington', 'United States', '98371'),\n",
       " ('The New Bike Store', 'Hanford Mall', None, 'Hanford', 'California', 'United States', '93230'),\n",
       " ('Third Bike Store', '2500 North Stemmons Freeway', None, 'Dallas', 'Texas', 'United States', '75201'),\n",
       " ('Thrilling Bike Tours', '4660 Rodeo Road', None, 'Santa Fe', 'New Mexico', 'United States', '87501'),\n",
       " ('Top Bike Market', '975 Princess Street', None, 'Kingston', 'Ontario', 'Canada', '7L'),\n",
       " ('Transport Bikes', '25130 South State Street', None, 'Sandy', 'Utah', 'United States', '84070'),\n",
       " ('Twelfth Bike Store', 'No. 25400 10665 Jasper Avenue', None, 'Edmonton', 'Alberta', 'Canada', 'T5'),\n",
       " ('Two Bike Shops', '35525-9th Street Sw', None, 'Puyallup', 'Washington', 'United States', '98371'),\n",
       " ('Two-Seater Bikes', '5700 Legacy Dr', None, 'Plano', 'Texas', 'United States', '75074'),\n",
       " ('Unique Bikes', '3900 S. 997th St.', None, 'Milwaukee', 'Wisconsin', 'United States', '53202'),\n",
       " ('Unsurpassed Bikes', 'Po Box 8035996', None, 'Dallas', 'Texas', 'United States', '75201'),\n",
       " ('Uttermost Bike Shop', 'Ellesfield Ave', None, 'Bracknell', 'England', 'United Kingdom', 'RG12 8TB'),\n",
       " ('Vast Bike Sales and Rental', 'Lewis County Mall', None, 'Chehalis', 'Washington', 'United States', '98532'),\n",
       " ('Weekend Bike Tours', '630 N. Capitol Ave.', None, 'San Jose', 'California', 'United States', '95112'),\n",
       " ('Western Bike Supplies', '566 S. Main', None, 'Cedar City', 'Utah', 'United States', '84720'),\n",
       " ('World Bike Discount Store', '3065 Santa Margarita Parkway', None, 'Trabuco Canyon', 'California', 'United States', '92679'),\n",
       " ('World of Bikes', '660 Lindbergh', None, 'Saint Louis', 'Missouri', 'United States', '63103'),\n",
       " ('A Cycle Shop', 'Heritage Mall', None, 'Albany', 'Oregon', 'United States', '97321'),\n",
       " ('A Great Bicycle Company', '6030 Robinson Road', None, 'Jefferson City', 'Missouri', 'United States', '65101'),\n",
       " ('Action Bicycle Specialists', 'Warrington Ldc Unit 25/2', None, 'Woolston', 'England', 'United Kingdom', 'WA1 4SY'),\n",
       " ('All Cycle Shop', '25111 228th St Sw', None, 'Bothell', 'Washington', 'United States', '98011'),\n",
       " ('All Cycle Shop', '8713 Yosemite Ct.', None, 'Bothell', 'Washington', 'United States', '98011'),\n",
       " ('Another Bicycle Company', '567 Sw Mcloughlin Blvd', None, 'Milwaukie', 'Oregon', 'United States', '97222'),\n",
       " ('Bicycle Accessories and Kits', 'Lewiston Mall', None, 'Lewiston', 'Idaho', 'United States', '83501'),\n",
       " ('Bicycle Lines Distributors', '6996 South Lindbergh', None, 'Saint Louis', 'Missouri', 'United States', '63103'),\n",
       " ('Bicycle Merchandise Warehouse', '409 Santa Monica Blvd.', None, 'Santa Monica', 'California', 'United States', '90401'),\n",
       " ('Big Cycle Mall', '9500b E. Central Texas Expressway', None, 'Killeen', 'Texas', 'United States', '76541'),\n",
       " ('Black Bicycle Company', 'Factory Merchants', None, 'Branson', 'Missouri', 'United States', '65616'),\n",
       " ('Blue Bicycle Company', 'Horizon Outlet Center', None, 'Monroe', 'Michigan', 'United States', '98272'),\n",
       " ('Brown Bicycle Company', 'University Town Center', None, 'San Diego', 'California', 'United States', '92102'),\n",
       " ('Center Cycle Shop', '99300 223rd Southeast', None, 'Bothell', 'Washington', 'United States', '98011'),\n",
       " ('Center Cycle Shop', '1318 Lasalle Street', None, 'Bothell', 'Washington', 'United States', '98011'),\n",
       " ('Central Bicycle Specialists', '6 The Farm, Liberty Road', None, 'Maidenhead', 'England', 'United Kingdom', 'SL67RJ'),\n",
       " ('Certified Bicycle Supply', '2345 West Spencer Road', None, 'Lynnwood', 'Washington', 'United States', '98036'),\n",
       " ('Closest Bicycle Store', 'Garamonde Drive, Wymbush', 'PO Box 4023', 'Milton Keynes', 'England', 'United Kingdom', 'MK8 8ZD'),\n",
       " ('Commerce Bicycle Specialists', 'Wharfdale Road', None, 'Berkshire', 'England', 'United Kingdom', 'RG11 5TP'),\n",
       " ('Commuter Bicycle Store', '2575 Bloor Street East', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V6'),\n",
       " ('Corner Bicycle Supply', '2511 Baker Road', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V7'),\n",
       " ('Courteous Bicycle Specialists', '995 Crescent', None, 'Richmond Hill', 'Ontario', 'Canada', 'L4E 3M5'),\n",
       " ('Cycle Clearance', '2255 254th Avenue Se', None, 'Albany', 'Oregon', 'United States', '97321'),\n",
       " ('Cycle Merchants', '575 Rue St Amable', None, 'Quebec', 'Quebec', 'Canada', 'G1R'),\n",
       " ('Cycles Wholesaler & Mfg.', 'Science Park South, Birchwood', 'Stanford House', 'Warrington', 'England', 'United Kingdom', 'WA3 7BH'),\n",
       " ('Discount Bicycle Specialists', '25269 Wood Dale Rd.', None, 'Wood Dale', 'Illinois', 'United States', '60191'),\n",
       " ('Downhill Bicycle Specialists', '725a The Arches', None, 'Berks', 'England', 'United Kingdom', 'SL4 1RH'),\n",
       " ('Exceptional Cycle Services', 'Wade Road', None, 'Basingstoke Hants', 'England', 'United Kingdom', 'RG24 8PL'),\n",
       " ('Exclusive Bicycle Mart', 'Butterfield Hse, 7-251 Chapel St', None, 'Lancaster', 'England', 'United Kingdom', 'LA1 1LN'),\n",
       " ('Exhilarating Cycles', 'Kensington Valley Shops', None, 'Howell', 'Michigan', 'United States', '48843'),\n",
       " ('Family Cycle Store', '660 Saint-Jacques, Bureau 400', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H8'),\n",
       " ('Finer Cycle Shop', '2500-622 5th Ave Sw', None, 'Calgary', 'Alberta', 'Canada', 'T2P 2G8'),\n",
       " ('First Cycle Store', '25250 N 90th St', None, 'Scottsdale', 'Arizona', 'United States', '85257'),\n",
       " ('Gasless Cycle Shop', '25 First Canadian Place', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V5'),\n",
       " ('General Cycle Storehouse', '30025 Wayburne Drive, Suite 275', None, 'Burnaby', 'British Columbia', 'Canada', 'V5G 4W1'),\n",
       " ('Golf and Cycle Store', '5500 Grossmont Center Drive', None, 'La Mesa', 'California', 'United States', '91941'),\n",
       " ('Grand Bicycle Stores', '48255 I-10 E. @ Eastpoint Blvd.', None, 'Baytown', 'Texas', 'United States', '77520'),\n",
       " ('Grand Cycle Store', '44606 N Division St', None, 'Spokane', 'Washington', 'United States', '99202'),\n",
       " ('Great Bicycle Supply', '455 256th St.', None, 'Moline', 'Illinois', 'United States', '61265'),\n",
       " ('Historic Bicycle Sales', 'Redford Plaza', None, 'Redford', 'Michigan', 'United States', '48239'),\n",
       " ('Imported and Domestic Cycles', '22589 West Craig Road', None, 'North Las Vegas', 'Nevada', 'United States', '89030'),\n",
       " ('Little Bicycle Supply Shop', 'Cedar Creek Stores', None, 'Mosinee', 'Wisconsin', 'United States', '54455'),\n",
       " ('Low Price Cycles', 'Suite 500 995 W. 11th Avenue', None, 'Mississauga', 'Ontario', 'Canada', 'L5A 1H6'),\n",
       " ('Machines & Cycles Store', '25245 Rue Sherbrooke Ouest', None, 'Montreal', 'Quebec', 'Canada', 'H1Y 2H5'),\n",
       " ('Main Bicycle Services', '60750 San Clemente', None, 'Hayward', 'California', 'United States', '94541'),\n",
       " ('Maintenance and Repair for Bicycles', 'Lone Star Factory', None, 'La Marque', 'Texas', 'United States', '77568'),\n",
       " ('Metro Cycle Shop', '2507 Pacific Ave S', None, 'Tacoma', 'Washington', 'United States', '98403'),\n",
       " ('Metropolitan Bicycle Supply', 'Paramount House', None, 'London', 'England', 'United Kingdom', 'W1N 9FA'),\n",
       " ('Modular Cycle Systems', '800 Interchange Blvd.', 'Suite 2501', 'Austin', 'Texas', 'United States', '78701'),\n",
       " ('Modular Cycle Systems', '165 North Main', None, 'Austin', 'Texas', 'United States', '78701'),\n",
       " ('Nearby Cycle Shop', 'Burgess Hill', 'Edward Way', 'West Sussex', 'England', 'United Kingdom', 'RH15 9UD'),\n",
       " ('Orange Bicycle Company', '123 W. Lake Ave.', None, 'Peoria', 'Illinois', 'United States', '61606'),\n",
       " ('Original Bicycle Supply Company', '2573 Dufferin Street', None, 'Toronto', 'Ontario', 'Canada', 'M4B 1V5'),\n",
       " ('Outstanding Cycles', 'North 93270 Newport Highway', None, 'Spokane', 'Washington', 'United States', '99202'),\n",
       " ('Professional Cycle Store', 'Arcadia Crossing', None, 'Phoenix', 'Arizona', 'United States', '85004'),\n",
       " ('Purple Bicycle Company', '2550 Middlefield Road', None, 'Scarborough', 'Ontario', 'Canada', 'M1V 4M2'),\n",
       " ('Reasonable Bicycle Sales', 'C/O Starpak, Inc.', None, 'Greeley', 'Colorado', 'United States', '80631'),\n",
       " ('Recommended Bicycles', '250 Kingsbridge Garden Circle', None, 'Mississauga', 'Ontario', 'Canada', 'L5B 3V4'),\n",
       " ('Red Bicycle Company', '99, Rue Saint-pierre', None, 'Pnot-Rouge', 'Quebec', 'Canada', 'J1E 2T7'),\n",
       " ('Remote Bicycle Specialists', 'No. 2000-25080 Beaver Hall Hill', None, 'Quebec', 'Quebec', 'Canada', 'G1R'),\n",
       " ('Retail Cycle Shop', 'Bayshore Mall', None, 'Eureka', 'California', 'United States', '95501'),\n",
       " ('Riding Cycles', 'Galashiels', None, 'Liverpool', 'England', 'United Kingdom', 'L4 4HB'),\n",
       " ('Rural Bicycle Supply', 'Suite #9902', None, 'North York', 'Ontario', 'Canada', 'M4C 4K6'),\n",
       " ('Rural Cycle Emporium', '6388 Lake City Way', None, 'Burnaby', 'British Columbia', 'Canada', 'V5A 3A6'),\n",
       " ('Safe Cycles Shop', '25915 140th Ave Ne', None, 'Bellevue', 'Washington', 'United States', '98004'),\n",
       " ('Safe Cycles Shop', '2681 Eagle Peak', None, 'Bellevue', 'Washington', 'United States', '98004'),\n",
       " ('Sellers of Cycles', '700 Se Sunnyside Road', None, 'Clackamas', 'Oregon', 'United States', '97015'),\n",
       " ('Serious Cycles', 'Suite 99320 255 - 510th Avenue S.W.', None, 'Calgary', 'Alberta', 'Canada', 'T2P 2G8'),\n",
       " ('Small Cycle Store', 'Horizon Outlet Center', None, 'Holland', 'Michigan', 'United States', '49423'),\n",
       " ('Suburban Cycle Shop', '994 Sw Cherry Park Rd', None, 'Troutdale', 'Oregon', 'United States', '97060'),\n",
       " ('Swift Cycles', '25500 Old Spanish Trail', None, 'Houston', 'Texas', 'United States', '77003'),\n",
       " ('The Bicycle Accessories Company', 'Vista Marketplace', None, 'Alhambra', 'California', 'United States', '91801'),\n",
       " ('Two Wheels Cycle Store', '77 Beale Street', 'Room 99767c', 'San Francisco', 'California', 'United States', '94109'),\n",
       " ('Unicycles, Bicycles, and Tricycles', 'Stonewood Mall', None, 'Downey', 'California', 'United States', '90241'),\n",
       " ('Valley Bicycle Distributors', '5867 Sunrise Boulevard', None, 'Citrus Heights', 'California', 'United States', '95610'),\n",
       " ('Valley Bicycle Specialists', 'Blue Ridge Mall', None, 'Kansas City', 'Missouri', 'United States', '64106'),\n",
       " ('Westside Cycle Store', '25550 Executive Dr', None, 'Elgin', 'Illinois', 'United States', '60120'),\n",
       " ('Yellow Bicycle Company', 'St. Louis Marketplace', None, 'Saint Louis', 'Missouri', 'United States', '63103')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CompanyName, AddressLine1, AddressLine2, City, \n",
    "    StateProvince, CountryRegion, PostalCode FROM\n",
    "    CustomerAW JOIN CustomerAddress ON (\n",
    "        CustomerAW.CustomerID=CustomerAddress.CustomerID) JOIN\n",
    "    Address ON (Address.AddressID=CustomerAddress.AddressID)\n",
    "    WHERE LOWER(CompanyName) LIKE '%bike%' OR \n",
    "        LOWER(CompanyName) LIKE '%cycle%'\n",
    "    ORDER BY CASE WHEN LOWER(CompanyName) LIKE '%bike%' THEN 1\n",
    "                  WHEN LOWER(CompanyName) LIKE '%cycle%' THEN 2\n",
    "             END, CompanyName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**Show the total order value for each CountryRegion. List by value with the highest first.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>countryregion</th>\n",
       "        <th>subt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>518096.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>347336.69</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United Kingdom', 518096.42), ('United States', 347336.69)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CountryRegion, ROUND(SUM(SubTotal), 2) subt FROM\n",
    "    Address JOIN SalesOrderHeader ON (\n",
    "        Address.AddressID=SalesOrderHeader.ShipToAddressID)\n",
    "    GROUP BY CountryRegion\n",
    "    ORDER BY subt DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Find the best customer in each region.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>countryregion</th>\n",
       "        <th>companyname</th>\n",
       "        <th>amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>Action Bicycle Specialists</td>\n",
       "        <td>108561.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Eastside Department Store</td>\n",
       "        <td>83858.43</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United Kingdom', 'Action Bicycle Specialists', 108561.83),\n",
       " ('United States', 'Eastside Department Store', 83858.43)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (-- statistic sheet with pseudo-rank for filtering\n",
    "    SELECT CountryRegion, CompanyName, ROUND(SUM(SubTotal), 2) amt,\n",
    "      RANK() OVER (\n",
    "        PARTITION BY CountryRegion ORDER BY SUM(SubTotal) DESC) AS ord FROM\n",
    "      Address JOIN SalesOrderHeader ON (\n",
    "        Address.AddressID=SalesOrderHeader.ShipToAddressID) JOIN\n",
    "      CustomerAW ON (\n",
    "          CustomerAW.CustomerID=SalesOrderHeader.CustomerID)\n",
    "      GROUP BY CountryRegion, CompanyName\n",
    ")\n",
    "SELECT CountryRegion, CompanyName, amt FROM t\n",
    "  WHERE t.ord=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
